# -*- coding: utf-8 -*-
'''
@Time    : 2021/4/27 21:38
<<<<<<< HEAD:xiaojie/HomeWork17.py
@Author  : xiaojie
=======
@Author  : 小杰
>>>>>>> origin/master:小杰/HomeWork17.py
@File    : pyMysql.py
'''
# import pymysql
# conn = pymysql.connect(host="49.233.39.160", port=3306, user="user", password="leboAa!#$123", database="lebo16",
#                        charset="utf8")
# # 设置cursor返回字典格式
# print(conn)
# cur = conn.cursor(pymysql.cursors.DictCursor)
# studentNo = input('请输入学号：')
# sql = "select * from students_0922 where studentNo = %s"
# result = cur.execute(sql, studentNo)   #这样赋值可以解决sql注入问题
# print(sql)
# print(result)
# select_01 = cur.fetchone()
# print(select_01)
# select_02 = cur.fetchone()
# print(select_02)
# 相对位置
# cur.scroll(2, mode="relative")
# select_04 = cur.fetchmany(3)
# select_05 = cur.fetchone()
# select_03 = cur.fetchall()
# cur.scroll(11, "absolute")
# scroll(size, mode) #相对，绝对
# select_06 = cur.fetchone()
# print(select_05)
# print(select_04)
# print(select_03)
# print(select_06)
#
# conn.commit()
# cur.close()
# conn.close()

from pymysql import *
while True:
    conn = connect(host="49.233.39.160", port=3306, user="user", password="leboAa!#$123", database="lebo16",
                   charset="utf8")
    # 获取游标
    cur = conn.cursor(cursor=cursors.DictCursor) # 设置cursor返回字典格式
    pd = int(input('你好,登录系统请回复:1-登录 2-注册 3-退出。请输入指令：\n'))
    # 登录
    if pd == 1:
        print("*********************开始登录")
        name = input('请输入姓名：\n')
        stuno = int(input('请输入学号：\n'))

        # 数据库表中查询是否含有该姓名和编号
        sql = "select * from students_0922 where studentNo = '%d' and name = '%s'" % (stuno, name)
        count = cur.execute(sql)
        conn.commit()
        result = cur.fetchone()
        print(sql,result)
        if result == 1:
            print(result)
            print("登录成功")
        else:
            print('用户名密码或密码错误')

        cur.close()
        conn.close()
    elif pd == 2:
        print('*********************注册用户')
        name1 = input('请输入姓名：\n')
        stuno1 = int(input('请输入学号：\n'))
        # 判断信息是否已被注册
        sql = "select * from students_0922 where studentNo = \"%s\"" % stuno1
        aa = cur.execute(sql)
        conn.commit()
        if aa == 1:
            print("用户名已存在，请重新注册")
        else:
            print("用户名不存在，请注册")
            sql = "insert into students_0922(name,studentNo) values (\"%s\",\"%d\")" % (name1, stuno1)
            cur.execute(sql)
            result = cur.fetchone()
            print(sql)
            conn.commit()
            print(result)
            print("注册成功")
        cur.close()
        conn.close()
    elif pd == 3:
        print('退出系统bye bye')
        break
    else:
        print("输入的命令不正确")
        cur.close()
        conn.close()
